package cn.sfa.service.routine;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
 
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.DateUtil;
import cn.sfa.entity.affixItem.SaleAffixItem;
import cn.sfa.entity.manage.SaleProduct;
import cn.sfa.entity.routine.SaleTrainInfo;
import cn.sfa.entity.routine.TrainTpye;
 
import cn.sfa.entity.train.SaleAgentTrain;
import cn.sfa.entity.train.SaleTrainProduct;
import cn.sfa.entity.train.SaleTrainTake;
import cn.sfa.entity.train.SaleTrainTakeInfo;
import cn.sfa.entity.train.SaleTrainer;

/**
 * 
 * 类名称：DailyRoutineService 类描述： 讲师服务类 创建人：lenovo 创建时间：2012-2-20 上午11:32:36
 * 修改人：lenovo 修改时间：2012-2-20 上午11:32:36 修改备注：
 * 
 * @version
 */
@Service
@Transactional
public class LecturerService {

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;

	/**
	 * 新建讲师（后台）
	 * @param model
	 * @param trianTpye
	 * @return
	 * @author hui
	 */
	public boolean addTrainer(SaleTrainer model, String trianTpye) {
		String sqlStr = "INSERT INTO SALETRAINER(SALETRAINERID,CREATEDATE,CREATEUSER,BRANCHID,TRAINERNAME,GOODATITEM,TRAINERLEVEL,TRAINERTYPE) "
				+ "values(:SALETRAINERID,:CREATEDATE,:CREATEUSER,:BRANCHID,:TRAINERNAME,:GOODATITEM,:TRAINERLEVEL,:TRAINERTYPE)";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALETRAINERID", model.getSaleTrainerId());
		paramMap.put("CREATEDATE", model.getCreateDate());
		paramMap.put("CREATEUSER", model.getCreateUid());
		paramMap.put("BRANCHID", model.getBranchId());
		paramMap.put("TRAINERNAME", model.getTrainerName());
		paramMap.put("GOODATITEM", model.getGoodAtItem());
		paramMap.put("TRAINERLEVEL", model.getTrainerLevel());
		paramMap.put("TRAINERTYPE", trianTpye);
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}

	/**
	 * 获取讲师信息（后台）
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param trainerName
	 * @param branchId
	 * @param trainerType
	 * @param trainerGrade
	 * @return
	 * @author hui
	 */
	public Pagination SeachTrainer(int currentPage,// 页码
			int numPerPage, // 页尺寸
			String url, 	// 分页url
			String trainerName, // 讲师姓名
			String branchId, // 所属机构
			String trainerType, // 兼讲类型
			String trainerGrade // 讲师等级
		) {
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select * from table(PKG_SFA_SALETRAINER.getTrainer(:trainerName,:branchId,:trainerType,:trainerGrade))");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("trainerName",trainerName);
		paramMap.put("branchId",branchId);
		paramMap.put("trainerType",trainerType);
		paramMap.put("trainerGrade",trainerGrade);
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);

	}

	/**
	 * 根据机构ID获取讲师(后台)
	 * @param branchId
	 * @return
	 * @author hui
	 */
	public List<SaleTrainer> getTrainerByBranchId(String branchId) {
		if (branchId.equals("BRA0000000000001")) {
			String sql = "SELECT SALETRAINERID,CREATEDATE,CREATEUSER,UPDATEDATE,UPDATEUSER,BRANCHID,TRAINERNAME,AGENTID,GOODATITEM,TRAINERLEVEL,TRAINSTARTDATE,TRAINSORT FROM SALETRAINER ";
			return jdbcTemplate.getJdbcOperations().query(sql,
					new BeanPropertyRowMapper<SaleTrainer>(SaleTrainer.class));
		} else {
			String sql = "SELECT SALETRAINERID,CREATEDATE,CREATEUSER,UPDATEDATE,UPDATEUSER,BRANCHID,TRAINERNAME,AGENTID,GOODATITEM,TRAINERLEVEL,TRAINSTARTDATE,TRAINSORT FROM SALETRAINER WHERE BRANCHID=:BRANCHID";
			Map<String, Object> paramMap = new HashMap<String, Object>();
			paramMap.put("BRANCHID", branchId);
			return jdbcTemplate.query(sql, paramMap,
					new BeanPropertyRowMapper<SaleTrainer>(SaleTrainer.class));
		}
	}

	/**
	 * 根据讲师ID获得讲师信息(后台)
	 * @param trainerid
	 * @return
	 * @author hui
	 */
	public SaleTrainer getTrainerByID(String trainerid) {
		String sqlStr = "select SALETRAINERID,CREATEDATE,CREATEUSER,UPDATEDATE,UPDATEUSER,BRANCHID,TRAINERNAME,AGENTID,GOODATITEM,TRAINERLEVEL,TRAINSTARTDATE,TRAINSORT,TRAINERTYPE from  saletrainer where saletrainerid=:saletrainerid";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("saletrainerid", trainerid);
		final SaleTrainer train = new SaleTrainer();
		jdbcTemplate.query(sqlStr, paramMap, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				train.setSaleTrainerId(rs.getString("SALETRAINERID"));
				train.setCreateDate(rs.getDate("CREATEDATE"));
				train.setCreateUser(rs.getString("CREATEUSER"));
				train.setUpdateDate(rs.getDate("UPDATEDATE"));
				train.setUpdateUser(rs.getString("UPDATEUSER"));
				train.setBranchId(rs.getString("BRANCHID"));
				train.setTrainerName(rs.getString("TRAINERNAME"));
				train.setAgentId(rs.getString("AGENTID"));
				train.setGoodAtItem(rs.getString("GOODATITEM"));
				train.setTrainerLevel(rs.getString("TRAINERLEVEL"));
				train.setTrainStartDate(rs.getDate("TRAINSTARTDATE"));
				train.setTrainSort(CommonUtil
						.isEmpty(rs.getString("TRAINSORT")) ? 0 : Double
						.valueOf(rs.getString("TRAINSORT")));
				train.setTrainerType(rs.getString("TRAINERTYPE"));
			}
		});
		return train;
	}

	/**
	 * 修改讲师(后台)
	 * @param model
	 * @return
	 * @author hui
	 */
	public boolean editTrainer(SaleTrainer model) {
		String sqlStr = "UPDATE SALETRAINER SET UPDATEDATE=:UPDATEDATE,UPDATEUSER=:UPDATEUSER,BRANCHID=:BRANCHID,TRAINERNAME=:TRAINERNAME,GOODATITEM=:GOODATITEM,TRAINERLEVEL=:TRAINERLEVEL WHERE SALETRAINERID=:SALETRAINERID ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("UPDATEDATE", model.getUpdateDate());
		paramMap.put("UPDATEUSER", model.getUpdateUser());
		paramMap.put("BRANCHID", model.getBranchId());
		paramMap.put("TRAINERNAME", model.getTrainerName());
		paramMap.put("GOODATITEM", model.getGoodAtItem());
		paramMap.put("TRAINERLEVEL", model.getTrainerLevel());
		paramMap.put("SALETRAINERID", model.getSaleTrainerId());
		int r = jdbcTemplate.update(sqlStr, paramMap);
		return r > 0;	 
	}

	/**
	 * 新建培训(后台)
	 * @param mode
	 * @return
	 * @author hui
	 */
	public boolean addtrain(SaleTrainTake mode) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "insert into SALETRAINTAKE (SALETRANTAKEID,CREATEDATE,CREATEUSER,"
				+ "UPDATEDATE,UPDATEUSER,TRAINTYPE,TRAINITEM,TRAINCONTENT,"
				+ "TRAINBRANCHID,TRAINSTATUS,SALETRAINERID,ISAFFIX,APPLYSTARTDATE,APPLYENDDATE,TRAINSTARTDATE,TRAINENDDATE,MAXAPPLYCONT) values (:SALETRANTAKEID,:CREATEDATE"
				+ ",:CREATEUSER,:UPDATEDATE,:UPDATEUSER,:TRAINTYPE,:TRAINITEM,:TRAINCONTENT,"
				+ ":TRAINBRANCHID,:TRAINSTATUS,:SALETRAINERID,:ISAFFIX,:APPLYSTARTDATE,:APPLYENDDATE,:TRAINSTARTDATE,:TRAINENDDATE,:MAXAPPLYCONT)";
		String createDateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
				.format(new Date());
		paramMap.put("SALETRANTAKEID", CommonUtil.jugeAndFixNull(mode
				.getSaleTrainTakeId()));
		paramMap.put("CREATEDATE", DateUtil.timeToString(createDateString));
		paramMap.put("CREATEUSER", CommonUtil.jugeAndFixNull(mode
				.getCreateUid()));
		paramMap.put("UPDATEDATE", CommonUtil.jugeAndFixNull(null));
		paramMap.put("UPDATEUSER", CommonUtil.jugeAndFixNull(mode
				.getUpdateUser()));
		paramMap.put("TRAINTYPE", CommonUtil
				.jugeAndFixNull(mode.getTrainType()));
		paramMap.put("TRAINITEM", CommonUtil
				.jugeAndFixNull(mode.getTrainItem()));
		paramMap.put("TRAINCONTENT", CommonUtil.jugeAndFixNull(mode
				.getTrainContent()));
		paramMap.put("TRAINBRANCHID", CommonUtil.jugeAndFixNull(mode
				.getTrainBranchid()));
		paramMap.put("TRAINSTATUS", CommonUtil.jugeAndFixNull(mode
				.getTrainStatus()));
		paramMap.put("SALETRAINERID", CommonUtil.jugeAndFixNull(mode
				.getSaleTrainerId()));
		paramMap.put("ISAFFIX", mode.getIsAffix());
		paramMap.put("APPLYSTARTDATE", mode.getApplyStartDate());
		paramMap.put("APPLYENDDATE", mode.getApplyEndDate());
		paramMap.put("TRAINSTARTDATE", mode.getTrainStartDate());
		paramMap.put("TRAINENDDATE", mode.getTrainEndDate());
		paramMap.put("MAXAPPLYCONT", mode.getMaxApplyCount());
		int i = jdbcTemplate.update(sqlStr, paramMap);
		return i > 0;
	}
	/**
	 * 更新培训
	 * @param mode
	 * @param saletrainerid
	 * @param saletrantakeid
	 * @return
	 * @author hui
	 */
	public boolean updatetrain(SaleTrainTake mode, String saletrainerid,
			String saletrantakeid) {
		String sqlStr = "update SALETRAINTAKE set UPDATEDATE =sysdate,UPDATEUSER = :UPDATEUSER,TRAINTYPE =:TRAINTYPE,TRAINITEM =:TRAINITEM, TRAINCONTENT =:TRAINCONTENT,TRAINBRANCHID =:TRAINBRANCHID,SALETRAINERID =:SALETRAINERID,APPLYSTARTDATE =:APPLYSTARTDATE,APPLYENDDATE =:APPLYENDDATE, TRAINSTARTDATE =:TRAINSTARTDATE,TRAINENDDATE =:TRAINENDDATE,MAXAPPLYCONT =:MAXAPPLYCONT where SALETRANTAKEID = :SALETRANTAKEID";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALETRANTAKEID", saletrantakeid);
		paramMap.put("UPDATEUSER", mode.getUpdateUser());
		paramMap.put("TRAINTYPE", mode.getTrainType());
		paramMap.put("TRAINITEM", mode.getTrainItem());
		paramMap.put("TRAINCONTENT", mode.getTrainContent());
		paramMap.put("TRAINBRANCHID", mode.getTrainBranchid());
		paramMap.put("SALETRAINERID", saletrainerid);
		paramMap.put("APPLYSTARTDATE", mode.getApplyStartDate());
		paramMap.put("APPLYENDDATE", mode.getApplyEndDate());
		paramMap.put("TRAINSTARTDATE", mode.getTrainStartDate());
		paramMap.put("TRAINENDDATE", mode.getTrainEndDate());
		paramMap.put("MAXAPPLYCONT", mode.getMaxApplyCount());
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}

	/**
	 * 前台得到培训(前台)
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param currentUserId
	 * @param branchId
	 * @param trainsType
	 * @param lecturerdate
	 * @param lecturerdate1
	 * @return
	 * @author hui
	 */
	public Pagination SeachTrains(int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String currentUserId,// 当前登录用户ID
			String branchId,// 当前登录用户的机构
			String trainsType,// 查询培训类型
			String lecturerdate,// 开始时间
			String lecturerdate1// 结束时间
	) {
		this.updateTrainStatus();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select * from table(PKG_SFA_SALELECTURER.getPreviousTrain(:currentUserId,:branchId,:trainsType,:lecturerdate,:lecturerdate1))");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("currentUserId", currentUserId);
		paramMap.put("branchId", branchId);
		paramMap.put("trainsType", trainsType);
		paramMap.put("lecturerdate", lecturerdate);
		paramMap.put("lecturerdate1", lecturerdate1);
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}

	/**
	 * 后台得到所有培训（后台）
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param loginbranchId
	 * @param trainTitle
	 * @param trainType
	 * @param trainStatus
	 * @param trianstartdate
	 * @param branchId
	 * @return
	 * @author hui
	 */
	public Pagination SeachTrain(int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String loginbranchId,// 机构ID
			String trainTitle, // 培训标题
			String trainType, // 培训类型
			String trainStatus, // 培训状态
			String trianstartdate, // 培训开始时间
			String branchId // 查询机构ID

	) {
		this.updateTrainStatus();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("select * from table(PKG_SFA_SALELECTURER.getBackTrain(:loginbranchId,:trainTitle,:trainType,:trainStatus,:trianstartdate,:branchId))");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("loginbranchId", loginbranchId);
		paramMap.put("trainTitle", trainTitle);
		paramMap.put("trainType", trainType);
		paramMap.put("trainStatus", trainStatus);
		paramMap.put("trianstartdate", trianstartdate);
		paramMap.put("branchId", branchId);
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}

	/**
	 * 学员插入数据库（后台）
	 * @param model
	 * @return
	 */
	public boolean intostudent(SaleAgentTrain model) {
		String sql = "select count(*) from SALEAGENTTRAIN where TRAINAGENTID=:TRAINAGENTID and SALETRANTAKEID=:SALETRANTAKEID";
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("TRAINAGENTID", model.getTrianAgentId());
		param.put("SALETRANTAKEID", model.getSaleTrainTakeId());
		int exists = jdbcTemplate.queryForInt(sql, param);
		if (exists > 0) {
			return false;
		}
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "insert into SALEAGENTTRAIN (SALEAGENTTRAINID,CREATEDATE,CREATEUSER,"
				+ "UPDATEDATE,UPDATEUSER,ISOUT,TRAINAGENTID,TRAINAGENTNAME,SALETRANTAKEID,TRAINGRADE,"
				+ "HONOURLIST,TRAINREMARK) values (:SALEAGENTTRAINID,:CREATEDATE"
				+ ",:CREATEUSER,:UPDATEDATE,:UPDATEUSER,:ISOUT,:TRAINAGENTID,:TRAINAGENTNAME,"
				+ ":SALETRANTAKEID,:TRAINGRADE,:HONOURLIST,:TRAINREMARK)";
		String createDateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
				.format(new Date());
		paramMap.put("SALEAGENTTRAINID", CommonUtil.jugeAndFixNull(model
				.getSaleAgentTrainId()));
		paramMap.put("CREATEDATE", DateUtil.timeToString(createDateString));
		paramMap.put("CREATEUSER", CommonUtil.jugeAndFixNull(model
				.getCreateUid()));
		paramMap.put("UPDATEDATE", CommonUtil.jugeAndFixNull(null));
		paramMap.put("UPDATEUSER", CommonUtil.jugeAndFixNull(model
				.getUpdateUser()));
		paramMap.put("ISOUT", model.getIsOut());
		paramMap.put("TRAINAGENTID", CommonUtil.jugeAndFixNull(model
				.getTrianAgentId()));
		paramMap.put("TRAINAGENTNAME", CommonUtil.jugeAndFixNull(model
				.getTrainAgentName()));
		paramMap.put("SALETRANTAKEID", CommonUtil.jugeAndFixNull(model
				.getSaleTrainTakeId()));
		paramMap.put("TRAINGRADE", model.getTrainGrade());
		paramMap.put("HONOURLIST", CommonUtil.jugeAndFixNull(model
				.getHonourList()));
		paramMap.put("TRAINREMARK", CommonUtil.jugeAndFixNull(model
				.getTrainRemark()));
		int i = jdbcTemplate.update(sqlStr, paramMap);
		return i > 0;
	}

	/**
	 * 获取培训总人数,评分人数以及平均分数(后台)
	 * @param saletrantakeid
	 * @return
	 */
	public SaleTrainInfo trainInfo(String saletrantakeid) {
		String sqlStr = " select saletrantakeid ,count(1) joinCount,"
				+ "count(traingrade) gradeCount, avg(nvl(traingrade,3)) "
				+ "gradeAvg  from saleagenttrain where saletrantakeid=:SALETRANTAKEID"
				+ " group by saletrantakeid";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALETRANTAKEID", saletrantakeid);

		final SaleTrainInfo trainInfo = new SaleTrainInfo();
		jdbcTemplate.query(sqlStr, paramMap, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				trainInfo.setSaleTrantakeId(rs.getString("saletrantakeid"));
				trainInfo.setJoinCount(rs.getInt("joinCount"));
				trainInfo.setGradeCount(rs.getInt("gradeCount"));
				trainInfo.setGradeAvg(rs.getFloat("gradeAvg"));
			}
		});
		return trainInfo;
	}

	/**
	 * 查询之前 更新培训的状态（后台）
	 *  @return
	 *  @author hui
	 */
	public void updateTrainStatus() {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr1 = "BEGIN update saletraintake set trainstatus ='TRAINSTATUS_01' where sysdate < APPLYSTARTDATE and  trainstatus not in ('TRAINSTATUS_00');"
				+ "update saletraintake set trainstatus ='TRAINSTATUS_02' where APPLYSTARTDATE< sysdate and  sysdate< APPLYENDDATE and  trainstatus not in ('TRAINSTATUS_00');"
				+ "update saletraintake set trainstatus ='TRAINSTATUS_03' where APPLYENDDATE< sysdate and  sysdate< TRAINSTARTDATE and  trainstatus not in ('TRAINSTATUS_00');"
				+ "update saletraintake set trainstatus ='TRAINSTATUS_04' where TRAINSTARTDATE< sysdate and  sysdate<TRAINENDDATE  and  trainstatus not in ('TRAINSTATUS_00');"
				+ "update saletraintake set trainstatus ='TRAINSTATUS_05' where TRAINENDDATE < sysdate and  trainstatus not in ('TRAINSTATUS_00','TRAINSTATUS_06');"
				+ "END;";
		jdbcTemplate.update(sqlStr1, paramMap);
	}
	 
	/**
	 * 培训总结,与平均分（后台）
	 * @param saletrantakeid
	 * @param sumup
	 * @param trainsort
	 * @return
	 */
	public boolean updatesumup(String saletrantakeid, String sumup,
			String trainsort) {
		Map<String, Object> param = new HashMap<String, Object>();
		String sqlStr1 = "update SALETRAINTAKE set sumup = :SUMUP , TRAINSTATUS = 'TRAINSTATUS_06' where SALETRANTAKEID = :SALETRANTAKEID";
		param.put("SUMUP", sumup);
		param.put("SALETRANTAKEID", saletrantakeid);
		try {
			int exists1 = jdbcTemplate.update(sqlStr1, param);
			if (exists1 == 0) {
				return false;
			}
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
			return false;
		}
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr2 = "update SALETRAINER set TRAINSORT=nvl(TRAINSORT,0)+:TRAINSORT  where SALETRAINERID "
				+ "= (select SALETRAINERID from SALETRAINTAKE where SALETRANTAKEID = :SALETRANTAKEID)";
		paramMap.put("TRAINSORT", Float.valueOf(trainsort));
		paramMap.put("SALETRANTAKEID", saletrantakeid);
		int exists2 = jdbcTemplate.update(sqlStr2, paramMap);
		return exists2 > 0;
	}
	
	/**
	 * 根据培训的ID查询信息(前,后台)
	 * @param saletrantakeid
	 * @return
	 * @author hui
	 */
	public SaleTrainTakeInfo getTrainTakeInfoById(String saletrantakeid) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "select * from table(PKG_SFA_SALELECTURER.getTrainInfo(:saletrantakeid))";
		paramMap.put("saletrantakeid", saletrantakeid);
		final SaleTrainTakeInfo train = new SaleTrainTakeInfo();
		jdbcTemplate.query(sqlStr, paramMap, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException { 
				train.setSaleTrainTypeName(rs.getString("traintypename"));
				train.setTrainItem(rs.getString("trainitem"));
				train.setSaleTrainerName(rs.getString("trainername"));
				train.setTrainContent(rs.getString("traincontent"));
				train.setApplyStartDate(rs.getTimestamp("applystartdate"));
				train.setApplyEndDate(rs.getTimestamp("applyenddate"));
				train.setTrainStartDate(rs.getTimestamp("trainstartdate"));
				train.setTrainEndDate(rs.getTimestamp("trainenddate"));
				train.setMaxApplyCount(rs.getInt("maxapplycont"));
				train.setSaleTrainStatusName(rs.getString("traintypename"));
				train.setIsAffix(rs.getString("isaffix"));
			}
		});
		return train;
	}

	/**
	 * 根据培训ID获得培训学员的信息（前台）
	 * 
	 * @param
	 * @return
	 */
	public Pagination getAgentTrainList(String SALETRANTAKEID) { 
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select TRAINAGENTID,TRAINAGENTNAME from SALEAGENTTRAIN ");
		sqlStr.append(" where SALETRANTAKEID=:SALETRANTAKEID ");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALETRANTAKEID", SALETRANTAKEID);
		return new Pagination(sqlStr.toString(), paramMap, 1,
				Integer.MAX_VALUE, "", jdbcTemplate);
	}

	/**
	 * 根据ID删除培训（后台）
	 * @param trainId
	 * @return
	 */
	public boolean delTrain(String trainId) {
		String sqlStr = " delete saleagenttrain where saletrantakeid=:trainId ";
		String sqlStr1 = " delete saleaffixitem where affixmainid=:trainId ";
		String sqlStr2 = " delete saletrainproduct where saletrantakeid=:trainId ";
		String sqlStr3 = " delete saletraintake where saletrantakeid=:trainId ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("trainId", trainId);
		jdbcTemplate.update(sqlStr, paramMap);
		jdbcTemplate.update(sqlStr1, paramMap);
		jdbcTemplate.update(sqlStr2, paramMap);
		return jdbcTemplate.update(sqlStr3, paramMap) > 0;
	}
	
	public boolean isSignUped(String userId, String trainTakeId) {
		String sqlStr = " select count(*) from saleagenttrain where TRAINAGENTID = :TRAINAGENTID AND SALETRANTAKEID =:SALETRANTAKEID  ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("TRAINAGENTID", userId);
		paramMap.put("SALETRANTAKEID", trainTakeId);
		return jdbcTemplate.queryForInt(sqlStr, paramMap) > 0;
	}

	/**
	 * 发布培训之后更新状态 （后台）
	 * @param statusID
	 * @return
	 * @author hui
	 */
	public boolean setIsSue(String statusID) {
		Map<String, Object> parammap = new HashMap<String, Object>();
		String sqlStr = " BEGIN update saletraintake st set st.trainstatus = 'TRAINSTATUS_01' where sysdate < st.APPLYSTARTDATE  and st.saletrantakeid = :statusID;"
				+ "update saletraintake st set st.trainstatus = 'TRAINSTATUS_02' where st.APPLYSTARTDATE < sysdate and sysdate < st.APPLYENDDATE and st.saletrantakeid = :statusID;"
				+ "update saletraintake st set st.trainstatus = 'TRAINSTATUS_03' where st.APPLYENDDATE < sysdate and sysdate < st.TRAINSTARTDATE and st.saletrantakeid = :statusID;"
				+ "update saletraintake st set st.trainstatus = 'TRAINSTATUS_04' where st.TRAINSTARTDATE < sysdate and sysdate < st.TRAINENDDATE and st.saletrantakeid = :statusID;"
				+ "update saletraintake st set st.trainstatus = 'TRAINSTATUS_05' where st.TRAINENDDATE < sysdate and st.saletrantakeid = :statusID; END;";
		parammap.put("statusID", statusID);
		return jdbcTemplate.update(sqlStr, parammap) > 0;
	}

	/**
	 * 培训评分 （前台）
	 * @param grade
	 * @param Trainagentid
	 * @param saletrantakeid
	 * @return
	 */
	public boolean setGrade(String grade, String Trainagentid,
			String saletrantakeid) {
		String sqlStr = "update saleagenttrain set traingrade = :grade where Trainagentid = :Trainagentid and saletrantakeid = :saletrantakeid ";
		Map<String, Object> parammap = new HashMap<String, Object>();
		parammap.put("grade", grade);
		parammap.put("Trainagentid", Trainagentid);
		parammap.put("saletrantakeid", saletrantakeid);
		return jdbcTemplate.update(sqlStr, parammap) > 0;
	}

	/**（后台）
	 * 根据学员ID和课程ID获取评分 （前台）
	 * @param saletrantakeid
	 * @param trainagentid
	 * @return
	 */
	public String getGrade(String saletrantakeid, String trainagentid) {
		String traingrade = null;
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "select traingrade from saleagenttrain where saletrantakeid = :saletrantakeid and trainagentid = :trainagentid";
		paramMap.put("saletrantakeid", saletrantakeid);
		paramMap.put("trainagentid", trainagentid);
		traingrade = jdbcTemplate.queryForObject(sqlStr, paramMap,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return rs.getString("traingrade");
					}
				});
		return traingrade;
	}

	/**
	 * 插入培训相关产品（后台）
	 * @param model
	 * @return
	 */
	public boolean insertTrainProduct(SaleTrainProduct model) {
		String sqlStr = "INSERT INTO saletrainproduct (SALETRAINPRODUCTID, CREATEDATE, CREATEUSER, SALETRANTAKEID, SALEPRODUCTID) "
				+ "VALUES (:SALETRAINPRODUCTID, :CREATEDATE, :CREATEUSER, :SALETRANTAKEID, :SALEPRODUCTID)";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALETRAINPRODUCTID", model.getSALETRAINPRODUCTID());
		paramMap.put("CREATEDATE", model.getCreateDate());
		paramMap.put("CREATEUSER", model.getCreateUser());
		paramMap.put("SALETRANTAKEID", model.getSALETRANTAKEID());
		paramMap.put("SALEPRODUCTID", model.getSALEPRODUCTID());
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}
	
	/**
	 * 删除培训相关产品 （后台）
	 * @param saletrantakeid
	 * @return
	 */
	public boolean delTrainProduct(String saletrantakeid) {
		String sqlStr = "delete saletrainproduct sp where sp.saletrantakeid = :saletrantakeid";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("saletrantakeid", saletrantakeid);
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}

	/**
	 * 根据培训ID该培训附件下载地址 （后台）
	 * 
	 * @param
	 * @return
	 */
	public List<SaleAffixItem> getAffixByID(String saletrantakeid) {
		String sqlStr = "select * from saleaffixitem where Affixmainid = :saletrantakeid";
		Map<String, Object> parammap = new HashMap<String, Object>();
		parammap.put("saletrantakeid", saletrantakeid);
		return jdbcTemplate.query(sqlStr, parammap,
				new BeanPropertyRowMapper<SaleAffixItem>(SaleAffixItem.class));
	}

	/**
	 * 根据附件ID该培训附件下载地址  （后台）
	 * 
	 * @param
	 * @return
	 */
	public List<SaleAffixItem> getAffixByAffixID(String saleaffixitemid) {
		String sqlStr = "select * from saleaffixitem where SALEAFFIXITEMID = :saleaffixitemid";
		Map<String, Object> parammap = new HashMap<String, Object>();
		parammap.put("saleaffixitemid", saleaffixitemid);
		return jdbcTemplate.query(sqlStr, parammap,
				new BeanPropertyRowMapper<SaleAffixItem>(SaleAffixItem.class));
	}

	/**
	 * 获得培训相关的产品 （后台）
	 * @param
	 * @return
	 */
	public List<SaleProduct> getProductByID(String saletrantakeid) {
		String sqlStr = "select sat.productname, sat.productid  "
				+ "from SALETRAINPRODUCT sa left join saleproduct sat "
				+ "on sa.SALEPRODUCTID = sat.SALEPRODUCTID "
				+ "where sa.SALETRANTAKEID = :saletrantakeid";
		Map<String, Object> parammap = new HashMap<String, Object>();
		parammap.put("saletrantakeid", saletrantakeid);
		return jdbcTemplate.query(sqlStr, parammap,
				new BeanPropertyRowMapper<SaleProduct>(SaleProduct.class));
	}

	/**
	 * 插入初始授课时间 （后台）
	 * @param saletrainerid
	 * @param trainStartDate
	 * @return
	 * @author hui
	 */
	public boolean setTrainStartTime(String saletrainerid, Date trainStartDate) {
		String sqlStr = "update saletrainer set trainstartdate = :trainStartDate  where saletrainerid=:saletrainerid";
		Map<String, Object> parammap = new HashMap<String, Object>();
		parammap.put("saletrainerid", saletrainerid);
		parammap.put("trainStartDate", trainStartDate);
		return jdbcTemplate.update(sqlStr, parammap) > 0;
	}

	/**
	 * 
	 * @param aletrainerid
	 * @return
	 * @author hui
	 */
	public int applyCount(String aletrainerid) {
		String sqlStr = "  select count(*) from saleagenttrain sc "
				+ "left join saletraintake stt on stt.saletrantakeid = sc.saletrantakeid "
				+ "where sc.saletrantakeid=:aletrainerid";
		Map<String, Object> parammap = new HashMap<String, Object>();
		parammap.put("aletrainerid", aletrainerid);
		int count = jdbcTemplate.queryForInt(sqlStr, parammap);
		return count;
	}

	/**
	 * 内 外勤 关联 （后台）
	 * 
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param agentcodeCode
	 * @param agentName
	 * @param branchid
	 * @return
	 * @author hui
	 */
	public Pagination GetInternal(
			int currentPage,// 页码
			int numPerPage,//尺寸
			String url,//分页
			String agentcode, //代理人代码
			String agentname,//代理人姓名
			String branchid,//机构id
			String traintype
	) {
		Map<String, Object> paraMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("select * from table(PKG_SFA_LECTURER.getInfo(:agentcode,:agentname,:branchid,:traintype)) ");
		paraMap.put("branchid", branchid);
		paraMap.put("agentcode", agentcode);
		paraMap.put("agentname", agentname);
		paraMap.put("traintype", traintype);
		return new Pagination(sqlStr.toString(), paraMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}

	/**
	 * 获取兼讲类型 （后台）
	 * 
	 * @return
	 * @author hui
	 */
	public List<TrainTpye> getTrainTpye() {
		String sql = "select sy.salerecordid,sy.chnname  from salesyscode sy  where sy.codeclass ='TRAINERTYPE'";
		return jdbcTemplate.getJdbcOperations().query(sql,
				new BeanPropertyRowMapper<TrainTpye>(TrainTpye.class));
	}

	/**
	 * 删除讲师 （后台）
	 * 
	 * @param saletrainerid
	 * @return
	 * @author hui
	 */
	public boolean deleteTrainer(String saletrainerid) {
		Map<String, Object> parammap = new HashMap<String, Object>();
		String sql = "delete saletrainer where saletrainerid=:saletrainerid";
		parammap.put("saletrainerid", saletrainerid);
		return jdbcTemplate.update(sql, parammap) > 0;
	}

}
